#encoding: utf-8
require 'mysql2'
require 'time'
# Connect to the database
@client = Mysql2::Client.new(:encoding => 'utf8mb4', :host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1403', :database => 'tutem_mall', :password => ENV['PSI_PASSWORD'])

load 'notice_utils.rb'      #sendDiscountNotice subscribe, phone, openid, discount

def update_user_discount(uid = 0)
    sql = <<~SQL
    SELECT o.uid, u.real_name as user_name, o.user_phone as phone, w.openid, w.subscribe, u.level, COUNT(*) as count, SUM(o.pay_price) - SUM(o.refund_price) as actual_paid_sum, MAX(o.pay_time) as latest_pay_time
    FROM tutem_mall.eb_store_order o left join tutem_mall.eb_user u on (o.uid = u.uid) left join tutem_mall.eb_wechat_user w on (o.uid = w.uid)
    WHERE o.pay_type = 'weixin' AND o.paid = 1 AND o.status >= 2 AND u.is_promoter = 0 AND u.level != 3
    SQL
    #WHERE o.pay_type = 'weixin' AND o.paid = 1 AND o.status >= 2 AND u.is_promoter = 0 AND u.level != 3 AND o.uid not in (select uid from tutem_mall.eb_tupal )

    sql += " AND o.uid = #{uid} " if uid != 0

    sql += <<~SQL
    GROUP BY o.uid
    ORDER BY actual_paid_sum DESC;
    SQL

    results = @client.query(sql)

    # Discount mapping based on actual_paid_sum
    discount_map = {
        1000 => 99, 20000 => 98, 50001 => 96, 100001 => 93, 200000 => 90
    }

    results.each do |row|
        uid = row['uid']
        level = row['level']
        actual_paid_sum = row['actual_paid_sum']
        latest_pay_time = row['latest_pay_time']
        openid = row['openid']
        phone = row['phone']
        subscribe = row['subscribe']

        discount = 100
        discount_map.each do |total, disc|
            discount = disc if actual_paid_sum >= total
        end

        sendDiscountNotice subscribe, phone, openid, discount

        print(' ') if actual_paid_sum < 1000
        next unless actual_paid_sum >= 1000

        # Update the eb_user_level table
        update_sql = <<~SQL
        UPDATE eb_user_level SET status = 0, is_del = 1 WHERE uid = #{uid} AND mark NOT LIKE '%TULP%'
        SQL
        @client.query(update_sql)
        #puts update_sql

        current_time = Time.now
        three_years_later = current_time + (3 * 365 * 24 * 60 * 60) # Add 3 years to current time
        valid_time = three_years_later.to_i # Convert to timestamp

        mark = "[#{current_time.strftime('%Y-%m-%d %H:%M:%S')}] TULP update uid##{uid} latest_pay_time:#{latest_pay_time} discount:#{discount} actual_paid_sum:#{actual_paid_sum.to_f.round(2)}"

        # Check if a record with the same uid and mark exists
        existing_record = @client.query("SELECT * FROM eb_user_level WHERE uid = #{uid} AND mark LIKE '%TULP%' AND is_del = 0 LIMIT 1").first

        if existing_record
            # Update the existing record
            existing_mark = existing_record['mark'];
            if existing_mark.include?("latest_pay_time:#{latest_pay_time}") || existing_mark.include?("FIXED")
                print '-'    #如果没有新订单，或者属于人工固定折扣，输出-
            else
                update_sql = <<~SQL
                UPDATE tutem_mall.eb_user_level SET `level_id` = #{level}, `grade` = #{level}, `valid_time` = #{valid_time},
                `is_forever` = 1, `status` = 1, `remind` = 0, `is_del` = 0, `discount` = #{discount}, mark = '#{mark}'
                WHERE `id` = #{existing_record['id']};
                SQL
                @client.query(update_sql)
                #puts update_sql
                print existing_mark.include?("discount:#{discount}") ? "'" : "^" #如果折扣未更新输出'否则输出^
            end
        else
            print "*"
            # Insert a new record
            insert_sql = <<~SQL
            INSERT INTO tutem_mall.eb_user_level (`uid`, `level_id`, `grade`, `valid_time`, `is_forever`, `mer_id`, `status`, `mark`, `remind`, `is_del`, `add_time`, `discount`)
            VALUES (#{uid}, #{level}, #{level}, #{valid_time}, 1, 0, 1, '#{mark}', 0, 0, #{current_time.to_i}, #{discount});
            SQL
            @client.query(insert_sql)
            #puts insert_sql
        end

    end
end
#update_user_discount 0

#用于恢复图腾之友会员的折扣
def resumeTupals20240626
    sql = 'select uid from tutem_mall.eb_tupal';
    results = @client.query(sql)
    results.each do |row|
        uid = row['uid']
        next if uid == 0
        next if uid == 99991
        puts "U# #{uid} discount resumed"
        update_user_discount(uid)
    end
    puts "Done"
end
